package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



public class Dao {
	
	static Connection conn = createConnection();
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */	
	public static Connection createConnection(){
		Connection conn = null;
		
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection("jdbc:mysql://localhost/carlettitest", "root", "1155665t");
			System.out.println("Database connection established");
		}catch(Exception e){
			System.err.println("Cannot connect to database server: "+e);
		}
		return conn;
	}
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static String[][] getAllMellemvarer() throws SQLException{
		int size = 0;
		String[][] result = null;
		Statement s = conn.createStatement();
		s.executeQuery("SELECT varenr, behandlingstid, antalenheder, foraeldetdato,nuvaerendedelbehandlingsid, `type`, behandling_navn, lagerplads_id  FROM mellemvare");
		ResultSet rs = s.getResultSet();
		int index = 0;
		if(rs != null){
			rs.beforeFirst();
			rs.last();
			size = rs.getRow();
			rs.first();
		}
		result = new String[size][8];
		do {
			result[index][0] = rs.getString("varenr");
			result[index][1] = rs.getString("behandlingstid");
			result[index][2] = rs.getString("antalenheder");
			result[index][3] = rs.getString("foraeldetdato");
			result[index][4] = rs.getString("nuvaerendedelbehandlingsid");
			result[index][5] = rs.getString("type");
			result[index][6] = rs.getString("behandling_navn");
			result[index][7] = rs.getString("lagerplads_id");
			index++;
				
		} while (rs.next());
			
		return result;
	}
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static void udtagAntal(int vareNr, int nyAntal) throws SQLException, InterruptedException{
		System.out.println("Update started");
		conn.setAutoCommit(false);
		conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
	
		PreparedStatement p; 
		p = conn.prepareStatement("SELECT antalenheder FROM mellemvare where varenr=(?)");
		p.setInt(1, vareNr);
		p.execute();
		ResultSet rs = p.getResultSet();
		rs.first();
		int antal = rs.getInt("antalenheder");
		p.close();
		
		p = conn.prepareStatement("UPDATE mellemvare SET antalenheder=? WHERE varenr=?");
		
		p.setInt(1, (antal-nyAntal));
		p.setInt(2, vareNr);
		p.executeUpdate();
		//Thread.sleep(60000);
		p.close();
		conn.commit();

		System.out.println("update comitted");
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */	
	public static String[][] getOverskredetMellemVarer() throws SQLException{
		int size = 0;
		String[][] result = null;
		
		Statement s = conn.createStatement();
		s.executeQuery("SELECT varenr, type, maxtoerretid-behandlingstid FROM mellemvare JOIN delbehandling on mellemvare.behandling_navn = delbehandling.delbehandlinger_navn AND mellemvare.delbehandlingsid = delbehandling.id WHERE mellemvare.tilstand = 1 OR mellemvare.tilstand = 4  ORDER BY maxtoerretid-behandlingstid");
		ResultSet rs = s.getResultSet();
		int index = 0;
			
		if(rs != null){
			rs.beforeFirst();
			rs.last();
			size = rs.getRow();
			rs.first();
		}
		
		result = new String[size][3];
		
		do {
			result[index][0] = rs.getString("varenr");
			result[index][1] = rs.getString("type");
			result[index][2] = rs.getString("maxtoerretid-behandlingstid");
			index++;
			
		} while (rs.next());
			
		return result;
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static int[] getMellemvareToerretider(int vareNr) throws SQLException{
		int[] result = null;
		
		PreparedStatement p;
		p = conn.prepareStatement("SELECT SUM(maxtoerretid), SUM(mintoerretid) FROM mellemvare JOIN delbehandling ON mellemvare.behandling_navn = delbehandlinger_navn WHERE varenr = (?)");
		p.setInt(1, vareNr);
		p.execute();
		ResultSet rs = p.getResultSet();
			
		result = new int[2];
		rs.first();
		result[0] = rs.getInt("SUM(maxtoerretid)");
		result[1] = rs.getInt("SUM(mintoerretid)");
				
		return result;
	} 
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */	
	public static String[][] getMellemvareMedType(String type) throws SQLException{
		String[][] result = null;
		int size = 0;
		
		PreparedStatement p; 
		p = conn.prepareStatement("SELECT varenr, nuvaerendedelbehandlingsid, count(*), type FROM mellemvare JOIN delbehandling ON mellemvare.behandling_navn = delbehandling.delbehandlinger_navn WHERE mellemvare.type = (?) GROUP BY varenr");
		p.setString(1, type);
		p.execute();
		ResultSet rs = p.getResultSet();
		
		int index = 0;
		if(rs != null){
			rs.beforeFirst();
			rs.last();
			size = rs.getRow();
			rs.first();
		}
		result = new String[size][3];
		
		do {
			result[index][0] = rs.getString("varenr");
			result[index][1] = (rs.getInt("nuvaerendedelbehandlingsid")+1) + "/"+rs.getString("count(*)") ;
			result[index][2] = rs.getString("type"); 
			index++;
		} while (rs.next());
		
		return result;
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static void indsaetMellemvare(int vareNr, String type, String behandlingnavn, int tilstand, int antalEnheder, int lagerpladsID) throws SQLException{
		
		PreparedStatement p;
		p = conn.prepareStatement("INSERT INTO mellemvare VALUES(?,?,?,?,?,?,?,?,?,?,?,?)");
		p.setInt(1, vareNr);
		p.setInt(2, antalEnheder);
		p.setInt(3, 70);
		p.setInt(4, 0);
		p.setBoolean(5, false);
		p.setDate(6, null);
		p.setInt(7, 0);
		p.setInt(8, tilstand);
		p.setString(9, type);
		p.setString(10, behandlingnavn);
		p.setInt(11, lagerpladsID);
		p.setString(12, behandlingnavn);
		p.executeUpdate();
		
		p.close();	
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static boolean isLagerpladsenLedig(String id) throws SQLException{
		int index = 0;
		boolean found = false;
		while(index < getAllMellemvarer().length && !found){
			if(getAllMellemvarer()[index][6].equals(id)){
				found = true;
			} else {
				index++;
			}
		}
		return found;		
	}

	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static void punktF() throws SQLException{		
		java.sql.CallableStatement proc = conn.prepareCall("{ call deletemellemvare()}");
		proc.execute();
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static ArrayList<String> getRoutines() throws SQLException{
		ArrayList<String> list = new ArrayList<String>();
		
		Statement s = conn.createStatement();
		s.execute("select SPECIFIC_NAME from information_schema.ROUTINES;");
		ResultSet rs = s.getResultSet();
		rs.first();
		
		do {
			list.add(rs.getString("SPECIFIC_NAME"));
		} while (rs.next());
		
		return list;
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static void callProcedure(String procedure) throws SQLException{
		CallableStatement proc;
		proc = conn.prepareCall("{ call " +procedure+ "()}");
		proc.execute();
		System.out.println("Procedure Excecuted");	
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static ArrayList<String> getLediglagerpladser() throws SQLException {
		ArrayList<String> list = new ArrayList<String>();
		
		Statement s = conn.createStatement();
		s.execute("SELECT id FROM lagerplads WHERE mellemvare_varenr IS NULL");
		ResultSet rs = s.getResultSet();
		rs.first();
		do {
		list.add(rs.getString("id"));
		} while (rs.next());
	
		return list;
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static ArrayList<String> getBehandlinger() throws SQLException{
		ArrayList<String> list = new ArrayList<String>();
		
		Statement s = conn.createStatement();
		s.execute("SELECT navn FROM behandling");
		ResultSet rs = s.getResultSet();
		rs.first();
		do {
			list.add(rs.getString("navn"));
		} while (rs.next());
		
		return list;		
	}
	
	/*
	 * @Author: Thomas Teilmann & Andy Vidkjaer
	 */
	public static boolean findesLagerpladsen(int id) throws SQLException{
		boolean found = false;
		
		PreparedStatement p;
		p = conn.prepareStatement("SELECT * FROM lagerplads WHERE id = (?)");
		p.setInt(1, id);
		p.execute();
		ResultSet rs = p.getResultSet();
		
		if(rs != null){
			found = true;
		} else {
		}
		
		p.close();
		
		return found;
	}
}
	

	
